package other.database;


import org.junit.Test;

import java.sql.*;

/**
 * @author bingo
 * @Description
 * @Date 2018/5/20
 */
public class UserDao {


    @Test
    public void insert(){

     String name = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
     String[]   depts ={"开发部","销售部","维修部","客服部","生产部","测试部","财务部"};
     double[]  salarys = {2000d,3000d,4000d,5600d,3500d,4500d,8000d,9000d,12000d,15000d};
     String[]   sex = {"男","女"};
      char[] names = name.toCharArray();
      long startTime = System.currentTimeMillis();
        PreparedStatement preparedStatement  =null;
        String sql = "insert into user(username,dept,salary,sex,createTime) values(?,?,?,?,now())";
        try {
            preparedStatement = DBUtil.getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        for(int i=0;i<100;i++) {
            try {
                Thread.sleep(100);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            String generateUsername = "";
          for (int j = 0; j < 10; j++) {
              int index = new Integer((int) (Math.random() * 62));
              generateUsername += names[index];
          }
          int deptIndex = new Integer((int) (Math.random() * depts.length));
          int salaryIndex = new Integer((int) (Math.random() * salarys.length));
          User user = new User();
          user.setUsername(generateUsername);
          user.setDept(depts[deptIndex]);
          user.setSalary(salarys[salaryIndex]);
          int sexIndex = new Integer((int) (Math.random() * sex.length));
          user.setSex(sex[sexIndex]);try {
                preparedStatement.setString(1, user.getUsername());
                preparedStatement.setString(2, user.getDept());
                preparedStatement.setDouble(3, user.getSalary());
                preparedStatement.setString(4, user.getSex());
                preparedStatement.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }

      }
      long endTime = System.currentTimeMillis();
      int second = (int) ((endTime-startTime)/1000);
        System.out.println("耗费的时间为:"+second/60+"分"+second%60+"秒");


    }


    public void updateUserInfoAdd(Connection connection){
        String sql  = "update user set money =money+50 where username=?";

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"zhangsan");
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }

    public void updateUserInfodesc(Connection connection){
        String sql  = "update user set money =money-50 where username=?";

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"lisi");
            preparedStatement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }

    /**执行存储过程  in 模式 没有返回值*/
    @Test
    public void doProcedure(){
        Connection connection = DBUtil.getConnection();
        CallableStatement cs = null;
        try {
            cs  = connection.prepareCall("{call p12(?)}");
            cs.setInt(1,100);
            cs.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    DBUtil.releaseDB(null,cs,connection);
    }



    /**存储过程带有 in 与out模式**/
    @Test
    public void doProcedureAndReturnValue(){
        Connection connection = DBUtil.getConnection();
        CallableStatement cs = null;
        try {
            cs  = connection.prepareCall("{call p8(?,?)}");
            cs.setInt(1,1);
            cs.registerOutParameter(2, Types.VARCHAR);
            cs.execute();
            int balance = cs.getInt(2);
            cs.executeQuery();
            System.out.println("balance："+balance);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBUtil.releaseDB(null,cs,connection);
    }


    public static void main(String[] args) {

        UserDao userDao = new UserDao();

        Connection connection = DBUtil.getConnection();
        try {
            connection.setAutoCommit(false);
            userDao.updateUserInfoAdd(connection);
            System.out.println(2/0);
            userDao.updateUserInfodesc(connection);

            connection.commit();

        } catch (Exception e) {
            System.out.println("回滚");
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
               DBUtil.releaseDB(null,null,connection);
        }
        }

}
